package cn.itcast.jsjwl.dao;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.itcast.jsjwl.domain.Teacher;
import cn.itcast.jsjwl.utils.JDBCUtils;

public class TeacherDao {

	public Teacher teacherLogin(String um, String up) throws SQLException {
		String sql="SELECT *  FROM tea_info WHERE teaid=? AND login_pwd=?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanHandler<Teacher>(Teacher.class),um,up);
	}

	
	public static int findTotalRecords() throws SQLException {
		String sql="SELECT COUNT(*) FROM tea_info";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		Long num=(Long)qr.query(sql, new ScalarHandler());
		return num.intValue();
	}


	public List<Teacher> findTeacherWithPage(int startIndex, int pageSize) throws SQLException {
		String sql="SELECT *FROM tea_info LIMIT ?,?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanListHandler<Teacher>(Teacher.class),startIndex,pageSize);
	}


	public void addTeacher(Teacher teacher) throws SQLException {
		String sql="INSERT INTO tea_info (teaid,name,sex,tel,job_title,faculty,login_pwd,class_id) VALUES( ? , ? , ?, ?, ?, ?, ?, ? );";
		Object[] params= {teacher.getTeaid(),teacher.getName(),teacher.getSex(),teacher.getTel(),teacher.getJob_title(),teacher.getFaculty(),teacher.getLogin_pwd(),teacher.getClass_id() };
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		qr.update(sql,params);	
	}


	public void delTeacherById(String tId) throws SQLException {
		String sql="delete FROM tea_info WHERE teaid=?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		qr.update(sql,tId);
		
	}


	public List<Teacher> findTeachersWithPageByStu(int startIndex, int pageSize) throws SQLException {
		String sql="SELECT *FROM tea_info LIMIT ?,?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanListHandler<Teacher>(Teacher.class),startIndex,pageSize);
	}


	public void updateTeacher(Teacher t) throws SQLException {
		String sql="UPDATE tea_info SET name= ? ,sex= ? ,tel= ? ,job_title= ? ,faculty= ? ,login_pwd= ? ,class_id= ? WHERE teaid= ? ";
		Object[] params= {t.getName(),t.getSex(),t.getTel(),t.getJob_title(),t.getFaculty(),t.getLogin_pwd(),t.getClass_id() ,t.getTeaid()};
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		qr.update(sql,params);	
	}

	

}
